package org.iteam.bank.signed.service;

import java.sql.SQLException;

import org.apache.commons.lang3.StringUtils;
import org.iteam.bank.core.Constant;
import org.iteam.bank.signed.model.P2PPayAccount;
import org.iteam.bank.signed.model.P2PUserInfo;
import org.iteam.db.SimpleDbRunner;
import org.iteam.db.exception.DbException;

public class P2PUserService {
	private P2PUserService() {
		super();
	}

	public static P2PUserService getInstance() {
		return Subclass.service;
	}

	private static final class Subclass {
		public static final P2PUserService service = new P2PUserService();
	}

	/**
	 * 是否存在该用户名
	 * 
	 * @param userName
	 * @return true: 存在，false:不存在
	 * @throws SQLException
	 */
	public boolean existUserByUserName(String userName) throws DbException {
		String sql = "SELECT COUNT(1)  FROM p2p_user where username = ? ";
		return new SimpleDbRunner().count(sql, userName) > 0;

	}

	/**
	 * 是否存在该身份证号码
	 * 
	 * @param idcard
	 * @return true: 存在，false:不存在
	 * @throws SQLException
	 */
	public boolean existUserByIdCard(String idcard) throws DbException {
		String sql = "SELECT COUNT(1)  FROM p2p_user where idcard = ? and idcardStatus != 0 ";
		return new SimpleDbRunner().count(sql, idcard) > 0;

	}

	/**
	 * 是否存在该银行卡号
	 * 
	 * @param cardno
	 * @return true: 存在，false:不存在
	 * @throws SQLException
	 */
	public boolean existUserByCardno(String cardno) throws DbException {
		String sql = "SELECT COUNT(1)  FROM p2p_user where cardno = ? and cardnoStatus != 0 ";
		return new SimpleDbRunner().count(sql, cardno) > 0;

	}

	/**
	 * 查询个人资料
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public P2PUserInfo showById(String id) throws DbException {
		return showById(null, id);
	}

	public P2PUserInfo showById(SimpleDbRunner runner, String id) throws DbException {
		if (runner == null) {
			runner = new SimpleDbRunner();
		}
		String sql = "select  pu.accountType,pu.p2pAccount_id,  pu.password, pu.solt, pu.isDeleted, pu.id,pu.email,pu.truename,pu.cardNoStatus,pu.mobile,pu.nickName,pu.userName,pu.idcard,pab.cardno,pab.bankCode,pab.bankName,pab.eCardNo,pab.protocolNo,pui.address commonAddress,pui.qq,pu.invitationCode,pu.registertime,pui.qq,pui.area,pui.city,pui.province,pui.address,pui.ecpTelephone from p2p_user pu inner join p2p_user_info pui on pui.id=pu.p2pUserInfo_id  LEFT JOIN p2p_account_bank pab on pab.p2pUserId =pu.id where pu.id = ? ";
		return runner.findBeanOne(P2PUserInfo.class, sql, id);
	}

	/**
	 * 查询支付信息
	 * 
	 * @param userName
	 * @return
	 * @throws DbException
	 */
	public P2PPayAccount showPayInfo(String userName) throws DbException {
		String sql = " select account,pay_password,ecard_no, protocol_no,name,certificate_no, card_no,bank_name, bank_id from pay_account where account = ? ";
		SimpleDbRunner dbRunner_pay = null;
		SimpleDbRunner dbRunner = null;
		P2PPayAccount p2pPayAccount = null;
		try {
			dbRunner_pay = new SimpleDbRunner(Constant.MYSQL_PAY);
			p2pPayAccount = dbRunner_pay.findBeanOne(P2PPayAccount.class, sql, userName);
			if (p2pPayAccount != null && StringUtils.isBlank(p2pPayAccount.getProtocol_no())) {
				String sql_user = "select   pab.protocolNo  from p2p_user pu  LEFT JOIN p2p_account_bank pab on pab.p2pUserId =pu.id where pu.userName = ? ";
				dbRunner = new SimpleDbRunner();
				String protocolNo = dbRunner.findColumnOne(String.class, sql_user, userName);
				p2pPayAccount.setProtocol_no(protocolNo);
			}
		} catch (DbException e) {
			throw e;
		} finally {
			dbRunner_pay = null;
			dbRunner = null;
		}
		return p2pPayAccount;
	}
}
